/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tecnick.jxhtmledit;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import com.microsoft.sqlserver.jdbc.SQLServerException;
import java.security.AccessController;
import java.security.PrivilegedAction;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *  We've hotcoded the DB creds for now. Still waiting on some CMS service
 *  that we can use to pull these creds in an encrypted format before
 *  making the connection. In addition, it'll let us be dependent on the
 *  CMS for DB specs, as opposed to us making the choices.
 * @author Jay
 */
public class CMSDatabaseInterface {

    public static Connection getConnection() throws SQLServerException {

        SQLServerDataSource ds = new SQLServerDataSource();
        ds.setUser("SQL2008_471290_siberiacms_user");
        ds.setPassword("vladivostok");
        ds.setServerName("sql2k801.discountasp.net");
        ds.setPortNumber(14330);
        ds.setDatabaseName("SQL2008_471290_siberiacms");
        Connection con = ds.getConnection();
        return con;
    }

    public ArrayList<String[]> getContentList(String[] query) throws SQLServerException, SQLException {
        Connection con = (Connection) AccessController.doPrivileged(new PrivilegedAction() {

            public Object run() {
                Connection c = null;
                try {
                    c = getConnection();
                } catch (SQLServerException ex) {
                }
                return c;
            }
        });

        String querycmd = "SELECT Name, Description, ID, ContentTypeID FROM Content WHERE ";
        for(int i = 0; i < query.length; i++ )
        {
            if(i != 0) querycmd += " OR ";
            String querypiece = "";
            //querypiece += " Name LIKE \'%" + MyBase64.encodeString(query[i]) + "%\' ";
            //querypiece += " OR Description LIKE \'%" + MyBase64.encodeString(query[i]) + "%\' ";
            String newkeyword = MyBase64.encodeString(query[i]).substring(0, MyBase64.encodeString(query[i]).length() - 3);
            querypiece += " Name LIKE \'%" + newkeyword + "%\' ";
            querypiece += " OR Description LIKE \'%" + newkeyword + "%\' ";
            querypiece += " OR ID LIKE \'%" + query[i] + "%\' ";
            querycmd += querypiece;
        }
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(querycmd);
        ArrayList<String[]> returnArray = new ArrayList<String[]>();
        while (rs.next()) {
            
            
            String querycmd2 = "SELECT Tag FROM ContentType WHERE ID = " + rs.getString("ContentTypeID");
            Statement stmt2 = con.createStatement();
            ResultSet rs2 = stmt2.executeQuery(querycmd2);
            String finaltag = "";
            while (rs2.next()) { finaltag = MyBase64.decodeString(rs2.getString("Tag")); }
            
            
            
            String[] cont_payload_triplet = {MyBase64.decodeString(rs.getString("Name")), MyBase64.decodeString(rs.getString("Description")), rs.getString("ID"), finaltag};
            returnArray.add(cont_payload_triplet);
        }
        con.close();
        return returnArray;

    }

    public ArrayList<String[]> getContentTypeList() throws SQLServerException, SQLException {
        Connection con = (Connection) AccessController.doPrivileged(new PrivilegedAction() {

            public Object run() {
                Connection c = null;
                try {
                    c = getConnection();
                } catch (SQLServerException ex) {
                }
                return c;
            }
        });

        String query = "SELECT * from ContentType";
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        ArrayList<String[]> returnArray = new ArrayList<String[]>();
        while (rs.next()) {
            String[] cont_payload_pair = {MyBase64.decodeString(rs.getString("Name")), MyBase64.decodeString(rs.getString("Tag"))};
            returnArray.add(cont_payload_pair);
        }
        con.close();
        return returnArray;
    }

    public static void main(String[] args) throws SQLServerException, SQLException {
        String connestring = "Data Source=tcp:sql2k801.discountasp.net;Initial Catalog=SQL2008_471290_siberiacms;User ID=SQL2008_471290_siberiacms_user;Password=vladivostok";
        Connection con = getConnection();

        String dbtime;
//        String dbUrl = "jdbc:mysql://sql2k801.discountasp.net/SQL2008_471290_siberiacms";
//        String dbClass = "com.mysql.jdbc.Driver";
        String query = "SELECT * from ContentType";

//
//            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//            Connection con = DriverManager.getConnection(dbUrl);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        System.out.println("BEGINNING QUERY LEN= " + rs.getFetchSize());

//
        while (rs.next()) {
            for (int i = 1; i <= 5; i++) {

                System.out.print(rs.getString(i) + " ");
            }
            //System.out.println(dbtime + "TIME");
            System.out.println();
        } //end while
        con.close();

        //end try


    }
}
